{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "intro",
   "metadata": {},
   "source": [
    "# 分组与聚合操作\n",
    "\n",
    "## Split-Apply-Combine 模式\n",
    "\n",
    "```\n",
    "原始数据 → Split(分组) → Apply(计算) → Combine(合并) → 结果\n",
    "```\n",
    "\n",
    "### 核心方法速查\n",
    "\n",
    "| 方法 | 功能 | 返回值 | 示例 |\n",
    "|------|------|--------|------|\n",
    "| `groupby()` | 分组 | GroupBy对象 | `df.groupby('col')` |\n",
    "| `agg()` | 聚合 | DataFrame | `group.agg('sum')` |\n",
    "| `transform()` | 转换 | Series | `group.transform('mean')` |\n",
    "| `apply()` | 应用函数 | DataFrame | `group.apply(func)` |\n",
    "| `filter()` | 过滤 | DataFrame | `group.filter(lambda x: len(x) > 5)` |\n",
    "\n",
    "---\n",
    "\n",
    "## 准备数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "load-data",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 加载数据\n",
    "try:\n",
    "    students = pd.read_csv('../data/demo/students.csv')\n",
    "    orders = pd.read_csv('../data/demo/orders.csv')\n",
    "    \n",
    "    # 合并数据以便分析\n",
    "    df = orders.merge(students[['学生ID', '专业', '年级']], on='学生ID')\n",
    "    \n",
    "    print(\"✓ 数据加载成功\")\n",
    "    print(f\"合并后数据: {df.shape}\")\n",
    "    print(f\"\\n前5行:\\n{df.head()}\")\n",
    "except FileNotFoundError:\n",
    "    print(\"请先运行第六讲生成示例数据\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "basic-groupby",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 一、基础分组 (groupby)\n",
    "\n",
    "### 单列分组\n",
    "\n",
    "类似Excel的【分类汇总】功能"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "basic-groupby-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 单列分组示例 ===\")\n",
    "\n",
    "# 1. 创建分组对象\n",
    "grouped = df.groupby('专业')\n",
    "print(f\"\\n分组数量: {len(grouped)}\")\n",
    "print(f\"分组名称: {list(grouped.groups.keys())}\")\n",
    "\n",
    "# 2. 基础聚合\n",
    "print(\"\\n各专业订单总额:\")\n",
    "print(grouped['订单金额'].sum().round(2))\n",
    "\n",
    "# 3. 多指标统计\n",
    "print(\"\\n各专业综合统计:\")\n",
    "major_stats = grouped['订单金额'].agg(['count', 'sum', 'mean']).round(2)\n",
    "major_stats.columns = ['订单数', '总金额', '平均金额']\n",
    "print(major_stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "multi-groupby",
   "metadata": {},
   "source": [
    "### 多列分组\n",
    "\n",
    "类似Excel的【多级分类汇总】"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "multi-groupby-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 多列分组示例 ===\")\n",
    "\n",
    "# 按专业和年级分组\n",
    "multi_grouped = df.groupby(['专业', '年级'])\n",
    "\n",
    "result = multi_grouped.agg({\n",
    "    '订单ID': 'count',\n",
    "    '订单金额': ['sum', 'mean']\n",
    "}).round(2)\n",
    "\n",
    "result.columns = ['订单数', '总金额', '平均金额']\n",
    "print(result.head(12))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "transform",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 二、分组转换 (transform)\n",
    "\n",
    "### transform vs agg\n",
    "\n",
    "| 特征 | agg | transform |\n",
    "|------|-----|-----   ------|\n",
    "| 返回值数量 | 每组一个值 | 每行一个值 |\n",
    "| 结果形状 | 压缩 | 保持原形状 |\n",
    "| 适用场景 | 汇总统计 | 标准化/归一化 |\n",
    "\n",
    "### 常见应用\n",
    "\n",
    "- 计算组内占比\n",
    "- 标准化/归一化\n",
    "- 与组均值的差异"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "transform-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== transform示例 ===\")\n",
    "\n",
    "# 1. 计算组内总额\n",
    "df['专业总金额'] = df.groupby('专业')['订单金额'].transform('sum')\n",
    "\n",
    "# 2. 计算占比\n",
    "df['占专业比例'] = (df['订单金额'] / df['专业总金额'] * 100).round(2)\n",
    "\n",
    "# 3. 计算与组均值的差异\n",
    "df['专业平均金额'] = df.groupby('专业')['订单金额'].transform('mean')\n",
    "df['与平均值差异'] = (df['订单金额'] - df['专业平均金额']).round(2)\n",
    "\n",
    "# 4. 组内排名\n",
    "df['专业内排名'] = df.groupby('专业')['订单金额'].rank(ascending=False, method='min')\n",
    "\n",
    "print(\"\\ntransform结果示例:\")\n",
    "print(df[['专业', '订单金额', '专业总金额', '占专业比例', '与平均值差异', '专业内排名']].head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "custom-agg",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 三、自定义聚合函数\n",
    "\n",
    "### 定义规则\n",
    "\n",
    "- 输入：Series（一组数据）\n",
    "- 输出：单个值或Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "custom-agg-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 自定义聚合函数示例 ===\")\n",
    "\n",
    "# 1. 简单自定义函数\n",
    "def value_range(x):\n",
    "    \"\"\"计算数值范围\"\"\"\n",
    "    return x.max() - x.min()\n",
    "\n",
    "def top_n_mean(x, n=3):\n",
    "    \"\"\"计算前N个值的平均\"\"\"\n",
    "    return x.nlargest(n).mean()\n",
    "\n",
    "# 2. 返回多个指标的函数\n",
    "def comprehensive_stats(x):\n",
    "    \"\"\"综合统计\"\"\"\n",
    "    return pd.Series({\n",
    "        '数量': len(x),\n",
    "        '总和': x.sum(),\n",
    "        '平均': x.mean(),\n",
    "        '中位数': x.median(),\n",
    "        '标准差': x.std(),\n",
    "        '范围': x.max() - x.min(),\n",
    "        '前3均值': x.nlargest(3).mean()\n",
    "    })\n",
    "\n",
    "# 3. 使用自定义函数\n",
    "print(\"\\n各专业订单金额范围:\")\n",
    "print(df.groupby('专业')['订单金额'].agg(value_range).round(2))\n",
    "\n",
    "print(\"\\n各商品类别综合统计:\")\n",
    "result = df.groupby('商品类别')['订单金额'].apply(comprehensive_stats).round(2)\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "apply",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 四、apply() 应用\n",
    "\n",
    "### apply vs agg\n",
    "\n",
    "| 特征 | agg | apply |\n",
    "|------|-----|-------|\n",
    "| 灵活性 | 中等 | 极高 |\n",
    "| 性能 | 快 | 较慢 |\n",
    "| 返回值 | 标量/Series | 任意类型 |\n",
    "| 适用场景 | 标准聚合 | 复杂计算 |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "apply-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== apply示例 ===\")\n",
    "\n",
    "# 复杂分析函数\n",
    "def analyze_group(group):\n",
    "    \"\"\"分组综合分析\"\"\"\n",
    "    return pd.Series({\n",
    "        '订单数': len(group),\n",
    "        '总金额': group['订单金额'].sum(),\n",
    "        '平均金额': group['订单金额'].mean(),\n",
    "        '最大订单': group['订单金额'].max(),\n",
    "        '最小订单': group['订单金额'].min(),\n",
    "        '完成率': (group['订单状态'] == '已完成').sum() / len(group) * 100,\n",
    "        '平均评分': group['评分'].mean() if group['评分'].notna().any() else 0\n",
    "    })\n",
    "\n",
    "# 按专业分组分析\n",
    "result = df.groupby('专业', group_keys=False).apply(analyze_group, include_groups=False).round(2)\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "filter",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 五、分组过滤 (filter)\n",
    "\n",
    "### 筛选满足条件的组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "filter-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== filter示例 ===\")\n",
    "\n",
    "# 1. 筛选订单数>30的专业\n",
    "result1 = df.groupby('专业').filter(lambda x: len(x) > 30)\n",
    "print(f\"\\n订单数>30的专业数据量: {len(result1)}\")\n",
    "print(result1.groupby('专业').size())\n",
    "\n",
    "# 2. 筛选平均订单金额>500的类别\n",
    "result2 = df.groupby('商品类别').filter(lambda x: x['订单金额'].mean() > 500)\n",
    "print(f\"\\n平均订单额>500的类别:\")\n",
    "print(result2['商品类别'].unique())\n",
    "\n",
    "# 3. 筛选总销售额Top3的专业\n",
    "major_total = df.groupby('专业')['订单金额'].sum().nlargest(3).index\n",
    "result3 = df[df['专业'].isin(major_total)]\n",
    "print(f\"\\nTop3专业: {list(major_total)}\")\n",
    "print(result3.groupby('专业')['订单金额'].sum().round(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "advanced",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 六、实战案例\n",
    "\n",
    "### 综合分析报告"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "advanced-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 综合销售分析报告 ===\")\n",
    "\n",
    "# 1. 各专业销售分析\n",
    "major_analysis = df.groupby('专业').agg({\n",
    "    '订单ID': 'count',\n",
    "    '订单金额': ['sum', 'mean', 'max'],\n",
    "    '评分': lambda x: x.dropna().mean() if x.notna().any() else 0\n",
    "}).round(2)\n",
    "major_analysis.columns = ['订单数', '总销售额', '平均订单额', '最大订单', '平均评分']\n",
    "print(\"\\n1. 各专业销售分析:\")\n",
    "print(major_analysis)\n",
    "\n",
    "# 2. 各商品类别×年级交叉分析\n",
    "cross_analysis = df.groupby(['商品类别', '年级']).agg({\n",
    "    '订单ID': 'count',\n",
    "    '订单金额': 'sum'\n",
    "}).round(2)\n",
    "cross_analysis.columns = ['订单数', '总销售额']\n",
    "print(\"\\n2. 商品类别×年级交叉分析（前15行）:\")\n",
    "print(cross_analysis.head(15))\n",
    "\n",
    "# 3. 找出每个专业的Top5订单\n",
    "print(\"\\n3. 各专业Top5订单:\")\n",
    "top5_by_major = df.groupby('专业', group_keys=False).apply(\n",
    "    lambda x: x.nlargest(5, '订单金额')[['专业', '订单ID', '订单金额']],\n",
    "    include_groups=False\n",
    ")\n",
    "print(top5_by_major.head(15))\n",
    "\n",
    "# 4. 计算各专业的同比增长（假设有日期数据）\n",
    "# 这里展示计算方式的框架\n",
    "print(\"\\n4. 支付方式分析:\")\n",
    "payment_analysis = df.groupby('支付方式').agg({\n",
    "    '订单ID': 'count',\n",
    "    '订单金额': 'sum'\n",
    "}).sort_values('订单金额', ascending=False).round(2)\n",
    "payment_analysis.columns = ['订单数', '总金额']\n",
    "payment_analysis['占比%'] = (payment_analysis['总金额'] / payment_analysis['总金额'].sum() * 100).round(2)\n",
    "print(payment_analysis)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "summary",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 操作速查表\n",
    "\n",
    "### 完整语法\n",
    "\n",
    "```python\n",
    "# 1. 基础分组\n",
    "df.groupby('col')['val'].sum()              # 单列分组求和\n",
    "df.groupby(['A', 'B'])['val'].mean()        # 多列分组求平均\n",
    "\n",
    "# 2. 聚合\n",
    "df.groupby('col')['val'].agg(['sum', 'mean'])       # 多指标\n",
    "df.groupby('col').agg({'A': 'sum', 'B': 'mean'})    # 多列不同函数\n",
    "\n",
    "# 3. transform\n",
    "df.groupby('col')['val'].transform('sum')           # 保持原形状\n",
    "df.groupby('col')['val'].transform(lambda x: x/x.sum())  # 自定义\n",
    "\n",
    "# 4. apply\n",
    "df.groupby('col').apply(custom_func)                # 自定义函数\n",
    "\n",
    "# 5. filter\n",
    "df.groupby('col').filter(lambda x: len(x) > 10)     # 筛选组\n",
    "df.groupby('col').filter(lambda x: x['val'].mean() > 100)  # 条件筛选\n",
    "\n",
    "# 6. 常用模式\n",
    "df.groupby('col')['val'].rank()                     # 组内排名\n",
    "df.groupby('col')['val'].cumsum()                   # 组内累计\n",
    "df.groupby('col')['val'].shift(1)                   # 组内移位\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "## 小结\n",
    "\n",
    "**核心概念**:\n",
    "1. **Split-Apply-Combine**: 分组操作的核心模式\n",
    "2. **groupby**: 创建分组对象\n",
    "3. **agg**: 聚合计算（返回压缩结果）\n",
    "4. **transform**: 组内计算（保持原形状）\n",
    "5. **apply**: 灵活的自定义操作\n",
    "6. **filter**: 按条件筛选组\n",
    "\n",
    "**使用建议**:\n",
    "- 简单聚合用`agg()`，性能好\n",
    "- 需要保持原数据形状用`transform()`\n",
    "- 复杂逻辑用`apply()`，但较慢\n",
    "- 多用内置函数，少用lambda\n",
    "- 大数据集注意性能\n",
    "\n",
    "**实用技巧**:\n",
    "- `transform()`常用于标准化、归一化\n",
    "- `apply()`可以返回DataFrame\n",
    "- `filter()`条件作用于整组\n",
    "- 组合使用多个方法完成复杂分析\n",
    "\n",
    "**完成！** 至此，Pandas数据处理的8讲内容全部优化完成。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:base] *",
   "language": "python",
   "name": "conda-base-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
